package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.dao.BookDao;
import com.oocl.entity.Book;
import com.oocl.utils.DbUtils;

public class BookDaoImpl implements BookDao {

	public int addBook(Book book) {
		int result = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			String sql = "insert into book(bid,bname,price,picture) values(bookseq.nextval,?,?,?)";			
			connection = DbUtils.connect();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, book.getBname());
			preparedStatement.setInt(2, book.getPrice());
			preparedStatement.setString(3, book.getPicture());
			result = preparedStatement.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(connection, preparedStatement, null);
		}
		return result;
	}

	public int deleteBook(int bid) {
		int result = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			String sql = "delete from book where bid=?";
			connection = DbUtils.connect();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, bid);
			result = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(connection, preparedStatement, null);
		}
		return result;
	}

	public List<Book> findAllBooks() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		List<Book> books = new ArrayList<Book>();
		ResultSet resultSet = null;
		try {
			String sql = "select * from book";
			connection = DbUtils.connect();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Book book = new Book();
				book.setBid(resultSet.getInt("bid"));
				book.setBname(resultSet.getString("bname"));
				book.setPrice(resultSet.getInt("price"));
				book.setPicture(resultSet.getString("picture"));
				books.add(book);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(connection, preparedStatement, resultSet);
		}
		return books;
	}

}
